﻿-- =============================================
-- Author:		Taylor Chase
-- Create date: April 28 2016
-- Description:	Takes an EntityType and either returns the entire list of valid relationships if the permission is null
--				OR the list of valid relationships whose permission matches or is lower than the passed permission 
-- =============================================
CREATE FUNCTION [dbo].[GetEntityRelationships] 
(	
	@EntityType nvarchar(100),
	@RC int,@WC int
)
RETURNS TABLE 
AS
RETURN 
(
	SELECT * FROM 
	(SELECT SRNameWithDir,RelationshipName,SuperRelationship,Entity1Type,Entity2Type,
	   IIF(@RC IS NULL,E1_Read_Direct_E2,IIF(@RC >= E1_Read_Direct_E2 AND E1_Read_Direct_E2 <> 0,@RC,0)) AS E1_Read_Direct_E2,
	   IIF(@RC IS NULL,E1_Read_Child_E2,IIF(@RC >= E1_Read_Child_E2,E1_Read_Child_E2,0)) AS E1_Read_Child_E2,
	   IIF(@WC IS NULL,E1_Write_Direct_E2,IIF(@WC >= E1_Write_Direct_E2 AND E1_Write_Direct_E2 <> 0,@WC,0)) AS E1_Write_Direct_E2,
	   IIF(@WC IS NULL,E1_Write_Child_E2,IIF(@WC >= E1_Write_Child_E2,E1_Write_Child_E2,0)) AS E1_Write_Child_E2
	FROM
	(SELECT ShortRelationshipName + '.forward' AS SRNameWithDir,RelationshipName,SuperRelationship,Entity1Type,Entity2Type,
	   dbo.SuperRelationshipPermission(ShortRelationshipName,0,'ReadDirect') AS E1_Read_Direct_E2,
	   dbo.SuperRelationshipPermission(ShortRelationshipName,0,'ReadChildren') AS E1_Read_Child_E2,
	   dbo.SuperRelationshipPermission(ShortRelationshipName,0,'WriteDirect') AS E1_Write_Direct_E2,
	   dbo.SuperRelationshipPermission(ShortRelationshipName,0,'WriteChildren') AS E1_Write_Child_E2
	FROM DefinedEntityRelationships WHERE Entity1Type = @EntityType 
	UNION
	SELECT ShortRelationshipName + '.reverse' AS SRNameWithDir,ReverseRelationship AS RelationshipName,SuperRelationship,Entity2Type AS Entity1Type,Entity1Type AS Entity2Type,
	   dbo.SuperRelationshipPermission(ShortRelationshipName,1,'ReadDirect') AS E1_Read_Direct_E2,
	   dbo.SuperRelationshipPermission(ShortRelationshipName,1,'ReadChildren') AS E1_Read_Child_E2,
	   dbo.SuperRelationshipPermission(ShortRelationshipName,1,'WriteDirect') AS E1_Write_Direct_E2,
	   dbo.SuperRelationshipPermission(ShortRelationshipName,1,'WriteChildren') AS E1_Write_Child_E2
	FROM DefinedEntityRelationships WHERE NOT ReverseRelationship IS NULL AND Entity2Type = @EntityType) AS EntitySec
	WHERE (E1_Read_Child_E2 <> 0 OR E1_Read_Direct_E2 <> 0 OR E1_Write_Child_E2 <> 0 OR E1_Write_Child_E2 <> 0)
	) AS Entity2Sec
	WHERE ((E1_Read_Child_E2 <> 0 OR E1_Read_Direct_E2 <> 0) AND NOT @RC IS NULL)
	OR ((E1_Write_Child_E2 <> 0 OR E1_Write_Child_E2 <> 0) AND NOT @WC IS NULL)
	OR (@RC IS NULL AND @WC IS NULL )
		
)